/**
 *  Copyright (C) 2008-2013  Telosys project org. ( http://www.telosys.org/ )
 *
 *  Licensed under the GNU LESSER GENERAL PUBLIC LICENSE, Version 3.0 (the "License");
 *  you may not use this file except in compliance with the License.
 *  You may obtain a copy of the License at
 *
 *          http://www.gnu.org/licenses/lgpl.html
 *
 *  Unless required by applicable law or agreed to in writing, software
 *  distributed under the License is distributed on an "AS IS" BASIS,
 *  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *  See the License for the specific language governing permissions and
 *  limitations under the License.
 */
package org.telosys.persistence.metadata;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;

import org.telosys.common.TelosysException;
import org.telosys.persistence.database.DatabaseManager;

/**
 * This Class allows to access Metadata from a Database.
 * 
 * NB : For future use (not yet qualified)
 * 
 * @author Olivier Laurendeau 
 * 
 */
public class DbMetaData {

	/**
	 * Return all information about the tables in the current DataBase.
	 * 
	 * @return DbTable[]
	 * @throws TelosysException
	 */
	public static DbTable[] getTables() throws TelosysException {
		return executeGetTable(null, null, null, null, -1, null);
	}

	/**
	 * Return all information about the tables in the DataBase identify by the given
	 * id
	 * 
	 * @param iDatabaseId
	 * @return DbTable[]
	 * @throws TelosysException
	 */
	public static DbTable[] getTables(int iDatabaseId) throws TelosysException {
		return executeGetTable(null, null, null, null, iDatabaseId, null);
	}

	/**
	 * Return all information about the tables in the DataBase identify by the
	 * connection
	 * 
	 * @param con
	 * @return DbTable[]
	 * @throws TelosysException
	 */
	public static DbTable[] getTables(Connection con) throws TelosysException {
		return executeGetTable(null, null, null, null, -1, con);
	}

//	/**
//	 * Return all information about the tables in the DataBase identify by the
//	 * session's connection
//	 * 
//	 * @param session
//	 * @return DbTable[]
//	 * @throws TelosysException
//	 */
//	public static DbTable[] getTables(DatabaseSession session) throws TelosysException {
//		return executeGetTable(null, null, null, null, -1, session.getConnection());
//	}

	/**
	 * Return all information about the tables identify by Catalog, Schema,
	 * tableName or/and Type in the current DataBase
	 * 
	 * @param sCatalog
	 * @param sSchema
	 * @param tableName
	 * @param arrayTableTypes
	 * @return DbTable[]
	 * @throws TelosysException
	 */
	public static DbTable[] getTables(String sCatalog, String sSchema, String tableName, String[] arrayTableTypes)
			throws TelosysException {
		return executeGetTable(sCatalog, sSchema, tableName, arrayTableTypes, -1, null);
	}

	/**
	 * Return all information about the tables identify by Catalog, Schema,
	 * tableName or/and Type in the DataBase identify by her id iDatabaseId
	 * 
	 * @param sCatalog
	 * @param sSchema
	 * @param tableName
	 * @param arrayTableTypes
	 * @param iDatabaseId
	 * @return DbTable[]
	 * @throws TelosysException
	 */
	public static DbTable[] getTables(String sCatalog, String sSchema, String tableName, String[] arrayTableTypes,
			int iDatabaseId) throws TelosysException {
		return executeGetTable(sCatalog, sSchema, tableName, arrayTableTypes, iDatabaseId, null);
	}

	/**
	 * Return all information about the tables identify by Catalog, Schema,
	 * tableName or/and Type in the DataBase identify by her Connection con
	 * 
	 * @param sCatalog
	 * @param sSchema
	 * @param tableName
	 * @param arrayTableTypes
	 * @param con
	 * @return DbTable[]
	 * @throws TelosysException
	 */
	public static DbTable[] getTables(String sCatalog, String sSchema, String tableName, String[] arrayTableTypes,
			Connection con) throws TelosysException {
		return executeGetTable(sCatalog, sSchema, tableName, arrayTableTypes, -1, con);
	}

//	/**
//	 * Return all information about the tables identify by Catalog, Schema,
//	 * tableName or/and Type in the DataBase identify by the session's connection
//	 * 
//	 * @param sCatalog
//	 * @param sSchema
//	 * @param tableName
//	 * @param arrayTableTypes
//	 * @param session
//	 * @return DbTable[]
//	 * @throws TelosysException
//	 */
//	public static DbTable[] getTables(String sCatalog, String sSchema, String tableName, String[] arrayTableTypes,
//			DatabaseSession session) throws TelosysException {
//		return executeGetTable(sCatalog, sSchema, tableName, arrayTableTypes, -1, session.getConnection());
//	}
	
	/**
	 * Return the table, specified by tableName from the current Database
	 * @param tableName
	 * @return DbTable
	 * @throws TelosysException
	 */
	public static DbTable getTable(String tableName) throws TelosysException {
		DbTable[] res = executeGetTable(null, null, tableName, null, -1, null);
		if(res.length > 0) {
			return res[0];
		} else {
			return null;
		}
	}
	
	/**
	 * Return the table, specified by tableName from the specified Database
	 * @param tableName
	 * @param iDatabaseId
	 * @return DbTable
	 * @throws TelosysException
	 */
	public static DbTable getTable(String tableName, int iDatabaseId) throws TelosysException {
		DbTable[] res = executeGetTable(null, null, tableName, null, iDatabaseId, null);
		if(res.length > 0) {
			return res[0];
		} else {
			return null;
		}
	}

	/**
	 * Return the table, specified by tableName from the specified Database
	 * @param tableName
	 * @param con
	 * @return DbTable
	 * @throws TelosysException
	 */
	public static DbTable getTable(String tableName, Connection con) throws TelosysException {
		DbTable[] res = executeGetTable(null, null, tableName, null, -1, con);
		if(res.length > 0) {
			return res[0];
		} else {
			return null;
		}
	}

//	/**
//	 * Return the table, specified by tableName from the specified Database
//	 * @param tableName
//	 * @param session
//	 * @return DbTable
//	 * @throws TelosysException
//	 */
//	public static DbTable getTable(String tableName, DatabaseSession session) throws TelosysException {
//		DbTable[] res = executeGetTable(null, null, tableName, null, -1, session.getConnection());
//		if(res.length > 0) {
//			return res[0];
//		} else {
//			return null;
//		}
//	}

	
	/**
	 * Ask the DataBase for information about tables
	 * 
	 * @param sCatalog
	 * @param sSchema
	 * @param sPattern
	 * @param arrayTableTypes
	 * @param iDatabaseId
	 * @param con
	 * @return DbTable[]
	 * @throws TelosysException
	 */
	private static DbTable[] executeGetTable(String sCatalog, String sSchema, String sPattern,
			String[] arrayTableTypes, int iDatabaseId, Connection con) throws TelosysException {
		DbTable[] arrayDbTable = new DbTable[0];

		// Select the database
		if (con == null) {
			if (iDatabaseId != -1) {
				con = DatabaseManager.getConnection(iDatabaseId);
			} else {
				con = DatabaseManager.getConnection();
			}
		}

		// Get DataBase MetaData
		DatabaseMetaData dbmd;
		try {
			dbmd = con.getMetaData();

			ResultSet rsTables = dbmd.getTables(sCatalog, sSchema, sPattern, arrayTableTypes);

			// Temporary storage
			LinkedList<DbTable> llDbTable = new LinkedList<DbTable>();

			while (rsTables.next()) {
				DbTable oCurrentTable = new DbTable();
				int i = 0;
				oCurrentTable.setTableCatalog(rsTables.getString(++i));
				oCurrentTable.setTableSchema(rsTables.getString(++i));
				oCurrentTable.setTableName(rsTables.getString(++i));
				oCurrentTable.setTableType(rsTables.getString(++i));
				oCurrentTable.setRemarks(rsTables.getString(++i));
				if (rsTables.getMetaData().getColumnCount() > i) {
					oCurrentTable.setTypeCatalog(rsTables.getString(++i));
					oCurrentTable.setTypeSchema(rsTables.getString(++i));
					oCurrentTable.setTypeName(rsTables.getString(++i));
					oCurrentTable.setSelfReferencingColName(rsTables.getString(++i));
					oCurrentTable.setRefGeneration(rsTables.getString(++i));
				}

				llDbTable.add(oCurrentTable);
			}

			rsTables.close();

			// To array
			arrayDbTable = new DbTable[llDbTable.size()];
			for (int i = 0; i < llDbTable.size(); i++) {
				arrayDbTable[i] = (DbTable) llDbTable.get(i);
			}
		} catch (SQLException e) {
			throw new TelosysException("SQL Error", e);
		}

		return arrayDbTable;
	}

	/**
	 * Return MetaData about the current DataBase
	 * 
	 * @return DatabaseMetaData
	 * @throws TelosysException
	 */
	public static DatabaseMetaData getMetaData() throws TelosysException {
		return executeGetMetaData(-1, null);
	}

	/**
	 * Return MetaData about the DataBase identify by her id iDatabaseId
	 * 
	 * @param iDatabaseId
	 * @return DatabaseMetaData
	 * @throws TelosysException
	 */
	public static DatabaseMetaData getMetaData(int iDatabaseId) throws TelosysException {
		return executeGetMetaData(iDatabaseId, null);
	}

	/**
	 * Return MetaData about the DataBase identify by her Connection con
	 * 
	 * @param con
	 * @return DatabaseMetaData
	 * @throws TelosysException
	 */
	public static DatabaseMetaData getMetaData(Connection con) throws TelosysException {
		return executeGetMetaData(-1, con);
	}

//	/**
//	 * Return MetaData about the DataBase identify by the session's connection
//	 * 
//	 * @param session
//	 * @return DatabaseMetaData
//	 * @throws TelosysException
//	 */
//	public static DatabaseMetaData getMetaData(DatabaseSession session) throws TelosysException {
//		return executeGetMetaData(-1, session.getConnection());
//	}

	/**
	 * Ask MetaData to the DataBase
	 * 
	 * @param iDatabaseId
	 * @param con
	 * @return DatabaseMetaData
	 * @throws TelosysException
	 */
	private static DatabaseMetaData executeGetMetaData(int iDatabaseId, Connection con) throws TelosysException {
		// Select the database
		DatabaseMetaData dmb;

		if (con == null) {
			if (iDatabaseId != -1) {
				con = DatabaseManager.getConnection(iDatabaseId);
			} else {
				con = DatabaseManager.getConnection();
			}
		}

		// Get DataBase MetaData
		try {
			dmb = con.getMetaData();
		} catch (SQLException e) {
			throw new TelosysException("SQL Error", e);
		}
		return dmb;
	}

	/**
	 * Return all Schemas of the current DataBase
	 * 
	 * @return String[]
	 * @throws TelosysException
	 * @throws SQLException
	 */
	public static String[] getSchemas() throws TelosysException, SQLException {
		return executeGetSchemas(-1, null);
	}

	/**
	 * Return all Schemas of the DataBase identify by her id iDatabaseId
	 * 
	 * @return String[]
	 * @throws TelosysException
	 */
	public static String[] getSchemas(int iDatabaseId) throws TelosysException {
		return executeGetSchemas(iDatabaseId, null);
	}

	/**
	 * Return all Schemas of the DataBase identify by her Connection
	 * 
	 * @return String[]
	 * @throws TelosysException
	 */
	public static String[] getSchemas(Connection con) throws TelosysException {
		return executeGetSchemas(-1, con);
	}

//	/**
//	 * Return all Schemas of the DataBase identify by the session's connection
//	 * 
//	 * @return String[]
//	 * @throws TelosysException
//	 */
//	public static String[] getSchemas(DatabaseSession session) throws TelosysException {
//		return executeGetSchemas(-1, session.getConnection());
//	}

	/**
	 * Ask the DataBase for her Schemas
	 * 
	 * @param iDatabaseId
	 * @param con
	 * @return String[]
	 * @throws TelosysException
	 */
	private static String[] executeGetSchemas(int iDatabaseId, Connection con) throws TelosysException {
		String[] arraySchemas = new String[0];

		// Select the database
		if (con == null) {
			if (iDatabaseId != -1) {
				con = DatabaseManager.getConnection(iDatabaseId);
			} else {
				con = DatabaseManager.getConnection();
			}
		}

		// Get DataBase MetaData
		DatabaseMetaData dbmd;
		try {
			dbmd = con.getMetaData();
			ResultSet rsTables = dbmd.getTables(null, null, null, null);

			// Temporary storage
			LinkedList<String> llSchemas = new LinkedList<String>();

			while (rsTables.next()) {
				llSchemas.add(rsTables.getString(2));
			}

			rsTables.close();

			// To array
			arraySchemas = new String[llSchemas.size()];
			for (int i = 0; i < llSchemas.size(); i++) {
				arraySchemas[i] = (String) llSchemas.get(i);
			}
		} catch (SQLException e) {
			throw new TelosysException("SQL Error", e);
		}

		return arraySchemas;
	}

	/**
	 * Return all columns of the indicated DbTable table, on the current
	 * DataBase
	 * 
	 * @param table
	 * @return DbColumn[]
	 * @throws TelosysException
	 */
	public static DbColumn[] getColumns(DbTable table) throws TelosysException {
		return executeGetColumns(table, -1, null,"%");
	}

	/**
	 * Return all columns of the indicated DbTable table, on the specified
	 * DataBase
	 * 
	 * @param table
	 * @return DbColumn[]
	 * @throws TelosysException
	 */
	public static DbColumn[] getColumns(DbTable table, int iDatabaseId) throws TelosysException {
		return executeGetColumns(table, iDatabaseId, null,"%");
	}

	/**
	 * Return all columns of the indicated DbTable table, on the specified
	 * DataBase
	 * 
	 * @param table
	 * @return DbColumn[]
	 * @throws TelosysException
	 */
	public static DbColumn[] getColumns(DbTable table, Connection con) throws TelosysException {
		return executeGetColumns(table, -1, con,"%");
	}

//	/**
//	 * Return all columns of the indicated DbTable table, on the specified DataBase
//	 * 
//	 * @param table
//	 * @return DbColumn[]
//	 * @throws TelosysException
//	 */
//	public static DbColumn[] getColumns(DbTable table, DatabaseSession session) throws TelosysException {
//		return executeGetColumns(table, -1, session.getConnection(),"%");
//	}
	
	/**
	 * Return the column, specified by columnName, of the indicated table from the current Database
	 * @param columnName
	 * @param table
	 * @return DbColumn
	 * @throws TelosysException
	 */
	public static DbColumn getColumn(String columnName, DbTable table) throws TelosysException {
		DbColumn[] res = executeGetColumns(table, -1, null, columnName);
		if(res.length > 0) {
			return res[0];
		} else {
			return null;
		}
	}
	
	/**
	 * Return the column, specified by columnName, of the indicated table from the specified Database
	 * @param columnName
	 * @param table
	 * @param iDatabaseId
	 * @return DbColumn
	 * @throws TelosysException
	 */
	public static DbColumn getColumn(String columnName, DbTable table, int iDatabaseId) throws TelosysException {
		DbColumn[] res = executeGetColumns(table, iDatabaseId, null, columnName);
		if(res.length > 0) {
			return res[0];
		} else {
			return null;
		}
	}
	
	/**
	 * Return the column, specified by columnName, of the indicated table from the specified Database
	 * @param columnName
	 * @param table
	 * @param con
	 * @return DbColumn
	 * @throws TelosysException
	 */
	public static DbColumn getColumn(String columnName, DbTable table, Connection con) throws TelosysException {
		DbColumn[] res = executeGetColumns(table, -1, con, columnName);
		if(res.length > 0) {
			return res[0];
		} else {
			return null;
		}
	}
	
//	/**
//	 * Return the column, specified by columnName, of the indicated table from the specified Database
//	 * @param columnName
//	 * @param table
//	 * @param session
//	 * @return DbColumn
//	 * @throws TelosysException
//	 */
//	public static DbColumn getColumn(String columnName, DbTable table, DatabaseSession session) throws TelosysException {
//		DbColumn[] res = executeGetColumns(table, -1, session.getConnection(), columnName);
//		if(res.length > 0) {
//			return res[0];
//		} else {
//			return null;
//		}
//	}
	
	/**
	 * Ask DataBase for information about the columns of a table
	 * 
	 * @param table
	 * @param iDatabaseId
	 * @param con
	 * @param columnName
	 * @return DbColumn[]
	 * @throws TelosysException
	 */
	private static DbColumn[] executeGetColumns(DbTable table, int iDatabaseId, Connection con, String columnName) throws TelosysException {
		DbColumn[] arrayDbColum = new DbColumn[0];

		// Select the database
		if (con == null) {
			if (iDatabaseId != -1) {
				con = DatabaseManager.getConnection(iDatabaseId);
			} else {
				con = DatabaseManager.getConnection();
			}
		}

		// Get DataBase MetaData
		DatabaseMetaData dbmd;
		try {
			dbmd = con.getMetaData();
			
			//Collect primary key
			ResultSet rsPk = dbmd.getPrimaryKeys(table.getTableCatalog(), table.getTableSchema(),
					table.getTableName());
			
			//LinkedList of pk's
			LinkedList<String> llPk = new LinkedList<String>();
			while(rsPk.next()) {
				llPk.add(rsPk.getString(4));
			}
			
			//Collect columns
			ResultSet rsColumns = dbmd.getColumns(table.getTableCatalog(), table.getTableSchema(),
					table.getTableName(), columnName);
			
			// Temporary storage
			LinkedList<DbColumn> llColumn = new LinkedList<DbColumn>();
			while (rsColumns.next()) {
				DbColumn oCurrentColumn = new DbColumn();
				int i = 0;
				oCurrentColumn.setTableCatalog(rsColumns.getString(++i));
				oCurrentColumn.setTableSchema(rsColumns.getString(++i));
				oCurrentColumn.setTableName(rsColumns.getString(++i));
				oCurrentColumn.setColumnName(rsColumns.getString(++i));
				oCurrentColumn.setDataType(rsColumns.getInt(++i));
				oCurrentColumn.setTypeName(rsColumns.getString(++i));
				oCurrentColumn.setColumnSize(rsColumns.getInt(++i));
				oCurrentColumn.setBufferLength(rsColumns.getInt(++i));
				oCurrentColumn.setDecimalDigits(rsColumns.getInt(++i));
				oCurrentColumn.setNumPrecRadix(rsColumns.getInt(++i));
				oCurrentColumn.setNullable(rsColumns.getInt(++i));
				oCurrentColumn.setRemarks(rsColumns.getString(++i));
				oCurrentColumn.setColumnDef(rsColumns.getString(++i));
				oCurrentColumn.setSqlDataType(rsColumns.getInt(++i));
				oCurrentColumn.setSqlDatetimeSub(rsColumns.getInt(++i));
				oCurrentColumn.setCharOctetLength(rsColumns.getInt(++i));
				oCurrentColumn.setOrdinalPosition(rsColumns.getInt(++i));
				oCurrentColumn.setIsNullable(rsColumns.getString(++i));
				if (rsColumns.getMetaData().getColumnCount() > i) {
					oCurrentColumn.setScopeCatalog(rsColumns.getString(++i));
					oCurrentColumn.setScopeSchema(rsColumns.getString(++i));
					oCurrentColumn.setScopeTable(rsColumns.getString(++i));
					oCurrentColumn.setSourceDataType(rsColumns.getString(++i));
				}

				//If column is primary key of her Table
				if(llPk.contains(oCurrentColumn.getColumnName())) {
					oCurrentColumn.setPrimaryKey(true);
				}
				
				llColumn.add(oCurrentColumn);
			}

			rsColumns.close();

			// Return array
			arrayDbColum = new DbColumn[llColumn.size()];
			for (int i = 0; i < llColumn.size(); i++) {
				arrayDbColum[i] = (DbColumn) llColumn.get(i);
			}
		} catch (SQLException e) {
			throw new TelosysException("SQL Error", e);
		}

		return arrayDbColum;
	}

}
